package com.futvan.z.system.zsql;
import com.futvan.z.framework.common.bean.Code;
import com.futvan.z.framework.common.bean.Result;
import com.futvan.z.framework.common.bean.ZTree;
import com.futvan.z.framework.core.z;
import com.futvan.z.framework.util.DateUtil;
import com.futvan.z.framework.util.StringUtil;
import com.futvan.z.framework.util.SystemUtil;
import com.futvan.z.system.z_sql_collect.z_sql_collect;
import com.futvan.z.system.zdb.z_db;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.beans.factory.annotation.Autowired;
import com.futvan.z.framework.core.SuperAction;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.*;

@Controller
public class ZsqlAction extends SuperAction{
	@Autowired
	private ZsqlService zsqlService;

	@RequestMapping(value="/open_sql_console")
	public ModelAndView open_sql_console(@RequestParam HashMap<String, String> bean) throws Exception {
		ModelAndView model = new ModelAndView("system/zsql/sql_console");
		model.addObject("datasource",z.sp.get("db_name"));
		model.addObject("isLimit",true);//是否默认分页
		return model;
	}

	/**
	 * 获取SQL执行记录
	 * @return
	 */
	private List<HashMap<String,String>> getRunSqlList() {
		String userid = GetSessionUserId();
		List<HashMap<String,String>> list = sqlSession.selectList("select","SELECT sqlinfo,create_time FROM z_sql WHERE userid = '"+userid+"' order by create_time desc limit 0,20");
		return list;
	}

	@RequestMapping(value = "/RunSQL")
	public @ResponseBody Result RunSQL(@RequestParam HashMap<String, String> bean) throws Exception {
		Result result = new Result();
		List<HashMap> rlist = new ArrayList<HashMap>();
		Map rmap = new HashMap();

		String datasource = bean.get("datasource");
		String db = bean.get("db");
		String sqlinfo = bean.get("sqlinfo");
		String userid = bean.get("userid");
		String isLimit = bean.get("isLimit");


		//根据分号分割SQL
		String [] sqls = sqlinfo.split(";");
		for (int i = 0; i < sqls.length; i++) {
			HashMap map = new HashMap();
			String sql = sqls[i].trim();

			if(sql.length()>6){
				//判读是否切换数据库
				String now_db = getSqlConsoleSqlSession(datasource).selectOne("selectone","SELECT DATABASE() db;");
				if(!db.equals(now_db)){
					getSqlConsoleSqlSession(datasource).update("update","USE "+db);
				}

				//获取SQL类型
				String sqltype = sql.substring(0,6).toLowerCase();
				//执行SQL
				if("update".equals(sqltype)){
					map.put("SqlType","update");
					int n = 0;
					try {
						n = getSqlConsoleSqlSession(datasource).update("update",sql);
						map.put("msg","执行update成功，修改记录数："+n);
						map.put("code",Code.SUCCESS);
					}catch (Exception e){
						map.put("code",Code.ERROR);
						map.put("msg","执行update失败|"+e.getMessage());
					}
				}else if("insert".equals(sqltype)){
					map.put("SqlType","insert");
					int n = 0;
					try {
						n = getSqlConsoleSqlSession(datasource).insert("insert",sql);
						map.put("msg","执行insert成功，插入记录数："+n);
						map.put("code",Code.SUCCESS);
					}catch (Exception e){
						map.put("code",Code.ERROR);
						map.put("msg","执行insert失败|"+e.getMessage());
					}
				}else if("delete".equals(sqltype)){
					map.put("SqlType","delete");
					int n = 0;
					try {
						n = getSqlConsoleSqlSession(datasource).delete("delete",sql);
						map.put("msg","执行delete成功，删除记录数："+n);
						map.put("code",Code.SUCCESS);
					}catch (Exception e){
						map.put("code",Code.ERROR);
						map.put("msg","执行delete失败|"+e.getMessage());
					}
				}else if("select".equals(sqltype)){
					map.put("SqlType","select");
					try {
						//默认取前1000条数据
						String temp_sql = sql;
						temp_sql = temp_sql.toLowerCase();
						if("true".equals(isLimit) && temp_sql.indexOf("limit")<0){
							sql = sql + " LIMIT 0, 100;";
						}

						//根据SQL解析创建字段
						HashMap table_data = new HashMap();
						LinkedHashMap tableHeadJson = AnalysisSQL(datasource, sql);
						table_data.put("tableHeadJson",tableHeadJson);
						List<HashMap<String,String>> list = getSqlConsoleSqlSession(datasource).selectList("select",sql);
						table_data.put("tableBodyJson",list);


						map.put("msg","执行select成功，查询记录数："+list.size());
						map.put("data",table_data);
						map.put("code",Code.SUCCESS);
					}catch (Exception e){
						map.put("code",Code.ERROR);
						map.put("msg","执行select失败|"+e.getMessage());
					}

				}else if(sqltype.indexOf("show")==0){
					map.put("SqlType","show");
					try {
						//根据SQL解析创建字段
						HashMap table_data = new HashMap();
						HashMap tableHeadJson = AnalysisSQL(datasource, sql);
						table_data.put("tableHeadJson",tableHeadJson);
						List<HashMap<String,String>> list = getSqlConsoleSqlSession(datasource).selectList("select",sql);
						table_data.put("tableBodyJson",list);

						map.put("msg","执行show成功，查询记录数："+list.size());
						map.put("data",table_data);
						map.put("code",Code.SUCCESS);
					}catch (Exception e){
						map.put("code",Code.ERROR);
						map.put("msg","执行show失败|"+e.getMessage());
					}
				}else{
					map.put("code",Code.ERROR);
					map.put("msg","无效SQL|"+sql);
				}

				rlist.add(map);

			}else{
				map.put("code",Code.ERROR);
				map.put("msg","无效SQL|"+sql);
				rlist.add(map);
			}
		}

		//SQL执行成功后，保存记录
		z_sql s = new z_sql();
		s.setZid(z.newZid("z_sql"));
		s.setCreate_time(DateUtil.getDateTime());
		s.setUserid(userid);
		s.setSqlinfo(sqlinfo);
		sqlSession.insert("z_sql_insert",s);

		//返回SQL执行结果列表信息
		rmap.put("rlist",rlist);

		//返回最近20条SQL执行记录
		rmap.put("run_sql_list",getRunSqlList());

		result.setSuccess(rmap);
		return result;
	}


	/**
	 * 解析执行的select字段
	 * @param datasource
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	protected LinkedHashMap AnalysisSQL(String datasource, String sql) throws Exception {
		LinkedHashMap map = new LinkedHashMap();
		SqlSession ss = getSqlConsoleSqlSession(datasource);
		Connection conn = ss.getConfiguration().getEnvironment().getDataSource().getConnection();
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(sql);
		ResultSetMetaData table = rs.getMetaData();
		for (int i = 1; i <= table.getColumnCount(); i++) {
			map.put(table.getColumnName(i),table.getColumnName(i));
		}
		st.close();
		conn.close();
		return map;
	}


	/**
	 * 获取数据库信息
	 * @param datasource
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="/getDbTree")
	public @ResponseBody Result getDbTree(String datasource) throws Exception {
		Result result = new Result();
		List<ZTree> treeList = new ArrayList<ZTree>();

		HashMap rmap = new HashMap();

		//获取当前库
		String now_db = getSqlConsoleSqlSession(datasource).selectOne("selectone","SELECT DATABASE() db;");


		//获取数据库列表
		List<ZTree> dbs = getDbList(datasource);
		treeList.addAll(dbs);


		//根据数据库获取表
		List<ZTree> tables = getDbTableList(datasource,dbs);
		treeList.addAll(tables);


		//生成用于SQL提示使用的信息
		Map<String,ArrayList<String>> sql_hint_map = new HashMap<String,ArrayList<String>>();

		//根据表获取字段信息
		List<ZTree> columns = getDbTableColumnList(datasource,tables,sql_hint_map,now_db);
		treeList.addAll(columns);


		//创建基础节点，表、视图、存储过程、触发器、函数、事件
		for (ZTree db : dbs) {
			//增加基础目录节点-表
			ZTree table_zTree = new ZTree();
			table_zTree.setZid(db.getZid()+".table");
			table_zTree.setName("表");
			table_zTree.setParentid(db.getZid());
			table_zTree.setOpen(false);
			treeList.add(table_zTree);

			//增加基础目录节点-视图
			ZTree view_zTree = new ZTree();
			view_zTree.setZid(db.getZid()+".view");
			view_zTree.setName("视图");
			view_zTree.setParentid(db.getZid());
			view_zTree.setOpen(false);
			treeList.add(view_zTree);

			//增加基础目录节点-存储过程
			ZTree procedure_zTree = new ZTree();
			procedure_zTree.setZid(db.getZid()+".procedure");
			procedure_zTree.setName("存储过程");
			procedure_zTree.setParentid(db.getZid());
			procedure_zTree.setOpen(false);
			treeList.add(procedure_zTree);

			//增加基础目录节点-函数
			ZTree function_zTree = new ZTree();
			function_zTree.setZid(db.getZid()+".function");
			function_zTree.setName("函数");
			function_zTree.setParentid(db.getZid());
			function_zTree.setOpen(false);
			treeList.add(function_zTree);

			//增加基础目录节点-触发器
			ZTree trigger_zTree = new ZTree();
			trigger_zTree.setZid(db.getZid()+".trigger");
			trigger_zTree.setName("触发器");
			trigger_zTree.setParentid(db.getZid());
			trigger_zTree.setOpen(false);
			treeList.add(trigger_zTree);

			//增加基础目录节点-事件
			ZTree event_zTree = new ZTree();
			event_zTree.setZid(db.getZid()+".event");
			event_zTree.setName("事件");
			event_zTree.setParentid(db.getZid());
			event_zTree.setOpen(false);
			treeList.add(event_zTree);
		}

		//获取最近20条SQL执行记录
		List<HashMap<String,String>> run_sql_list = getRunSqlList();

		//获取所有的收藏记录
		String userid = GetSessionUserId();
		List<z_sql_collect> sql_collects_list = getSqlCollectList(userid);

		//返回数据
		rmap.put("treeList",treeList);
		rmap.put("dbs",dbs);
		rmap.put("sql_hint_map",sql_hint_map);
		rmap.put("now_db",now_db);
		rmap.put("run_sql_list",run_sql_list);
		rmap.put("sql_collects_list",sql_collects_list);

		result.setSuccess(rmap);
		return result;
	}

	/**
	 * 获取所有的收藏记录
	 * @param userid
	 * @return
	 */
	private List<z_sql_collect> getSqlCollectList(String userid) {
		String sql = "select * from z_sql_collect where userid = '"+userid+"' order by create_time desc";
		List<z_sql_collect> list = sqlSession.selectList("z_sql_collect_select_sql",sql);
		return list;
	}

	private List<ZTree> getDbTableColumnList(String datasource, List<ZTree> tables, Map<String,ArrayList<String>> sql_hint_map, String now_db) throws Exception {
		List<ZTree> treeList = new ArrayList<ZTree>();
		ArrayList<String> colunm_hint_list = new ArrayList<String>();
		for (ZTree table : tables) {
			colunm_hint_list = new ArrayList<String>();

			//查询数据
			List<HashMap<String,String>> list = getSqlConsoleSqlSession(datasource).selectList("select","SHOW FULL COLUMNS FROM  "+table.getZid()+";");

			//封装数据
			for (HashMap<String,String> columnMap : list) {
				ZTree zTree = new ZTree();
				zTree.setZid(table.getZid()+"."+columnMap.get("Field"));
				zTree.setName(columnMap.get("Field")+"["+columnMap.get("Comment")+":"+columnMap.get("Type")+"]");
				zTree.setParentid(table.getZid());
				zTree.setOpen(false);
				treeList.add(zTree);
				zTree.setNodeType("column");
				if(table.getZid().indexOf(now_db+".")==0){
					colunm_hint_list.add(columnMap.get("Field"));
				}

			}

			//判读是当前库
			if(table.getZid().indexOf(now_db+".")==0){
				String hint_table = table.getZid().substring(table.getZid().indexOf(".")+1);
				sql_hint_map.put(hint_table,colunm_hint_list);
			}


		}

		return treeList;
	}

	private List<ZTree> getDbTableList(String datasource, List<ZTree> dbs) throws Exception {
		List<ZTree> treeList = new ArrayList<ZTree>();
		for (ZTree db : dbs) {
			//查询数据
			List<String> list = getSqlConsoleSqlSession(datasource).selectList("selectone","SHOW TABLES FROM "+db.getZid()+";");

			//封装数据
			for (String table : list) {
				ZTree zTree = new ZTree();
				zTree.setZid(db.getZid()+"."+table);
				zTree.setName(table);
				zTree.setParentid(db.getZid()+".table");
				zTree.setOpen(false);
				zTree.setNodeType("table");
				treeList.add(zTree);
			}

		}

		return treeList;
	}

	private List<ZTree> getDbList(String datasource) throws Exception {
		List<ZTree> treeList = new ArrayList<ZTree>();

		//查询数据
		List<String> list = getSqlConsoleSqlSession(datasource).selectList("selectone","SHOW DATABASES;");

		//封装数据
		for (String dbname : list) {
			if(!"__recycle_bin__".equals(dbname)
					&& !"information_schema".equals(dbname)
					&& !"mysql".equals(dbname)
					&& !"performance_schema".equals(dbname)
					&& !"sys".equals(dbname)){
				ZTree zTree = new ZTree();
				zTree.setZid(dbname);
				zTree.setName(dbname);
				zTree.setParentid("");
				zTree.setOpen(false);
				zTree.setClick("DbOnClick('"+dbname+"')");
				zTree.setNodeType("db");
				treeList.add(zTree);
			}
		}
		return treeList;
	}


	/**
	 * 获取SQL控制台使用的session
	 * @param datasource
	 * @return
	 * @throws Exception
	 */
	private SqlSessionTemplate getSqlConsoleSqlSession(String datasource) throws Exception {
		SqlSessionTemplate session = null;
		if(z.isNotNull(datasource)){
			session = z.dbsSqlConsole.get(datasource);
			if(z.isNull(session)){
				z.Exception("数据源无效，无法获取连接|datasource："+datasource);
			}
		}else{
			session = z.dbsSqlConsole.get(z.sp.get("db_name"));
		}

		return session;
	}


	/**
	 * 备份数据库
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="/BackupDb")
	public ResponseEntity<byte[]> BackupDb(@RequestParam HashMap<String,String> bean) throws Exception {
		String dbid = bean.get("dbid");
		String dbname = bean.get("dbname");
		if(z.isNotNull(dbid)){
			if(z.isNotNull(dbname)){
				//创建文件头信息
				HttpHeaders headers = new HttpHeaders();
				String file_name = dbid+"_"+dbname+"_"+DateUtil.getDateTime("yyyyMMddHHmm")+".sql";
				headers.setContentDispositionFormData("attachment", file_name);
				headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
				//备份数据库
				byte[] file = BackupMySQL(dbid,dbname);
				//返回文件流
				return new ResponseEntity<byte[]>(file,headers, HttpStatus.CREATED);
			}else{
				z.Exception("执行备份数据异常|dbname is null");
				return null;
			}
		}else{
			z.Exception("执行备份数据异常|dbid is null");
			return null;
		}

	}

	/**
	 * 备份MySQL数据库
	 * @return
	 */
	private byte[] BackupMySQL(String dbid, String dbname) throws Exception {
		byte[] file = new byte[0];
		z_db db = z.dbsMap.get(dbid);
		if(z.isNotNull(db)){
			String path = getFileSavePath();
			StringBuffer rinfo;
			if("z".equals(dbid)){
				rinfo = SystemUtil.dos(path+"/mysqldump -h "+z.sp.get("db_ip")+" --user="+z.sp.get("db_username")+" --password="+z.sp.get("db_password")+" --port="+z.sp.get("db_port")+" "+dbname);
			}else {
				rinfo = SystemUtil.dos(path+"/mysqldump -h "+db.getDbip()+" --user="+db.getUsername()+" --password="+db.getPassword()+" --port="+db.getDb_port()+" "+dbname);
			}
			if(z.isNotNull(rinfo)){
				file = rinfo.toString().getBytes();
			}
		}else{
			z.Exception("执行备份数据异常|dbname is null");
		}
		return file;
	}


	/**
	 * 收藏SQL
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value = "/SQLCollect")
	public @ResponseBody Result SQLCollect(@RequestParam HashMap<String, String> bean) throws Exception {
		Result result = new Result();
		String sqlinfo = bean.get("sqlinfo");
		String userid = bean.get("userid");
		String name = StringUtil.UrlDecode(bean.get("name"));

		//保存SQL
		z_sql_collect collect = new z_sql_collect();
		String number = z.newNumber();
		collect.setZid(number);
		collect.setName(name);
		collect.setNumber(number);
		collect.setCreate_time(DateUtil.getDateTime());
		collect.setSqlinfo(sqlinfo);
		collect.setUserid(userid);
		int n = sqlSession.insert("z_sql_collect_insert", collect);
		//保存成功
		if(n==1){
			List<z_sql_collect> sql_collects_list = getSqlCollectList(userid);
			result.setSuccess(sql_collects_list);
		}else{
			result.setError("收藏SQL失败|n="+n);
		}
		return result;
	}

}

